# -*- coding: utf-8 -*-
import sqlite3
conn=sqlite3.connect(r'cd.db3')
conn.text_factory = str
try:
	conn.execute('''CREATE TABLE rpt_companycountry(
		companyid TEXT REFERENCES [company]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		companyname TEXT,
		countryid INTEGER REFERENCES [country]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		countryname TEXT,
		boxs INTEGER NOT NULL DEFAULT 0,
		cds INTEGER,
		PRIMARY KEY ([companyid],[countryid])
	)''')
	conn.commit()
except:
	pass

conn.execute('replace into rpt_companycountry select cd_companyid,company,cd_countryid,country,sum(cd_box),count(cd_countryid) from cd,company,country where cd.cd_companyid=company.id and cd.cd_countryid=country.id group by cd_companyid,cd_countryid')
conn.commit()
conn.close()
